package com.ecsolutions.dao;

import com.ecsolutions.entity.Prenda_Entity;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.List;

/**
 * Created by Administrator on 2017/7/24.
 */
@Repository
public interface Prenda_DAO {
    // 证件类型
    @Select("SELECT distinct cstldes,cstcod2 FROM TACSTMAS where codtype = 'ID'")
    @ResultType(HashMap.class)
    List<HashMap<String,String>> getIdType();

    //select 年收入币种
    @Select("select distinct ccy,ccyname from TACCYMAS")
    @ResultType(HashMap.class)
    List<HashMap<String,String>> getCcy();

    //select 保险公司名称
    @Select("select distinct name from InsuranceCompanyText")
    @ResultType(HashMap.class)
    List<HashMap<String,String>> getinsuranceList();

    // 贷款卡编码查询出的信息
    @Select("SELECT pIdTypeId as pidtypeid,lastName as mortgagorname,BorrowerContactAddress as contactaddress,BorrowerContactNumber as contactphone FROM LoanApplicant WHERE TRIM(ethnic) = TRIM(#{ethnic})")
    @ResultType(Prenda_Entity.class)
    Prenda_Entity getByEthnic(@Param("ethnic") String ethnic);

    // 客户号查询出的信息
    @Select("SELECT * FROM loanapplicantpledge WHERE TRIM(custcod) = TRIM(#{custcod})")
    @ResultType(Prenda_Entity.class)
    List<Prenda_Entity> getByCustcod(@Param("custcod") String custcod);

    // ID查询出的信息
    @Select("SELECT * FROM loanapplicantpledge WHERE TRIM(id) = TRIM(#{id})")
    @ResultType(Prenda_Entity.class)
    Prenda_Entity getById(@Param("id") String id);

    // line_no查询出List<Pledge_entit>信息
    @Select("SELECT * FROM loanapplicantpledge WHERE TRIM(line_no) = TRIM(#{line_no})")
    @ResultType(Prenda_Entity.class)
    List<Prenda_Entity> getByLine_no(@Param("line_no") String line_no);

    // line_no查询出的最大num值
    @Select("SELECT Max(num) FROM loanapplicantpledge WHERE TRIM(line_no) = TRIM(#{line_no})")
    @ResultType(int.class)
    int getByLineno(@Param("line_no") String line_no);

    // line_no和num查询出的id值
    @Select("SELECT id FROM loanapplicantpledge WHERE TRIM(line_no) = TRIM(#{line_no}) and TRIM(num) = TRIM(#{num})")
    @ResultType(String.class)
    String getByLine_noAndNum(@Param("line_no") String line_no, @Param("num") int num);

    //保存抵押信息
    @Insert({"insert into loanapplicantpledge(line_no,custcod,id,mode1,amountccy,amount,valueccy,value,contractnumber,signeddate,bankaccountname,bankaccounttype,bankname,bankaccount,bankaccountccy,bankaccountbalance,bankaccountistrust,bankpledgename,bankpledgeownertype,bankpledgeeffectivedate,bankpledgelastdate,bankisfreezed,ethnic,pidtypeid,pidno,mortgagorname,contactaddress,contactphone,cerorg,cercustomer,cerno,ceraccount,cereffectivedate,cerlastdate,ceramount,cdname,cdtype,cdno,cdaccount,cdbank,cdisselfbank,cdamount,cdisstoppay,notestype,notesno,notesamount,notesbank,notessigneddate,noteslastdate,notesaddress,wrowner,wrorg,wrno,wrname,wrstandard,wrunit,wrnumber,wrdeliverystaff,wracceptancestaff,wrgoodsvalue,wrstoragecosts,wrhaveinsurance,wrinsurancebegindate,wrinsuranceenddate,ladorg,ladno,ladcontractno,ladcommoditycoding,ladshippingunit,ladgoods,ladrule,ladcharacter,laddeliverynumber,laddeliveryaddress,laddrawer,stockownername,stocknumber,stockno,stocknature,stockholdersname,stocksecuritiesaccountno,stockinstitutions,stockregno,stockinvestorsaccount,stockcode,stockpledgeref,stockpledgenumber,stockpledgeregdate,intprotype,intproownername,intproproveorg,intpropatentnumber,intproregorg,intproregdate,intproteddate,intproprovedate,intpropledgeno,intproorg,chargeorg,chargelicenceno,chargelastdate,chargeregorg,accrecproveperson,accrecowner,accrecamountccy,accrecamount,carproveperson,carproduction,carno,carbrand,cartype,carengineno,carchassisno,carprice,accrec_remark,bankaccount_remark,car_remark,cd_remark,cer_remark,charge_remark,intpro_remark,lad_remark,notes_remark,stock_remark,wr_remark,nd_remark,other_remark,chargeproject,pledgecustcod,nd_kind,nd_id,nd_rate,nd_deadline,nd_amount,nd_bank,nd_begindate,nd_enddate,nd_address,other_name,other_unit,other_number,other_address,num,stateid,createtime,refid)","values(#{line_no,jdbcType=VARCHAR},#{custcod,jdbcType=VARCHAR},emp_num_seq.nextval,#{mode1,jdbcType=VARCHAR},#{amountccy,jdbcType=CHAR},#{amount,jdbcType=NUMERIC},#{valueccy,jdbcType=CHAR},#{value,jdbcType=NUMERIC},#{contractnumber,jdbcType=VARCHAR},#{signeddate,jdbcType=TIMESTAMP},#{bankaccountname,jdbcType=VARCHAR},#{bankaccounttype,jdbcType=VARCHAR},#{bankname,jdbcType=VARCHAR},#{bankaccount,jdbcType=VARCHAR},#{bankaccountccy,jdbcType=CHAR},#{bankaccountbalance,jdbcType=NUMERIC},#{bankaccountistrust,jdbcType=NUMERIC},#{bankpledgename,jdbcType=VARCHAR},#{bankpledgeownertype,jdbcType=NUMERIC},#{bankpledgeeffectivedate,jdbcType=TIMESTAMP},#{bankpledgelastdate,jdbcType=TIMESTAMP},#{bankisfreezed,jdbcType=NUMERIC},#{ethnic,jdbcType=VARCHAR},#{pidtypeid,jdbcType=CHAR},#{pidno,jdbcType=VARCHAR},#{mortgagorname,jdbcType=VARCHAR},#{contactaddress,jdbcType=VARCHAR},#{contactphone,jdbcType=VARCHAR},#{cerorg,jdbcType=VARCHAR},#{cercustomer,jdbcType=VARCHAR},#{cerno,jdbcType=VARCHAR},#{ceraccount,jdbcType=VARCHAR},#{cereffectivedate,jdbcType=TIMESTAMP},#{cerlastdate,jdbcType=TIMESTAMP},#{ceramount,jdbcType=NUMERIC},#{cdname,jdbcType=VARCHAR},#{cdtype,jdbcType=NUMERIC},#{cdno,jdbcType=VARCHAR},#{cdaccount,jdbcType=VARCHAR},#{cdbank,jdbcType=VARCHAR},#{cdisselfbank,jdbcType=NUMERIC},#{cdamount,jdbcType=NUMERIC},#{cdisstoppay,jdbcType=NUMERIC},#{notestype,jdbcType=NUMERIC},#{notesno,jdbcType=VARCHAR},#{notesamount,jdbcType=NUMERIC},#{notesbank,jdbcType=VARCHAR},#{notessigneddate,jdbcType=TIMESTAMP},#{noteslastdate,jdbcType=TIMESTAMP},#{notesaddress,jdbcType=VARCHAR},#{wrowner,jdbcType=VARCHAR},#{wrorg,jdbcType=VARCHAR},#{wrno,jdbcType=VARCHAR},#{wrname,jdbcType=VARCHAR},#{wrstandard,jdbcType=VARCHAR},#{wrunit,jdbcType=VARCHAR},#{wrnumber,jdbcType=NUMERIC},#{wrdeliverystaff,jdbcType=VARCHAR},#{wracceptancestaff,jdbcType=VARCHAR},#{wrgoodsvalue,jdbcType=NUMERIC},#{wrstoragecosts,jdbcType=NUMERIC},#{wrhaveinsurance,jdbcType=NUMERIC},#{wrinsurancebegindate,jdbcType=TIMESTAMP},#{wrinsuranceenddate,jdbcType=TIMESTAMP},#{ladorg,jdbcType=VARCHAR},#{ladno,jdbcType=VARCHAR},#{ladcontractno,jdbcType=VARCHAR},#{ladcommoditycoding,jdbcType=VARCHAR},#{ladshippingunit,jdbcType=VARCHAR},#{ladgoods,jdbcType=VARCHAR},#{ladrule,jdbcType=VARCHAR},#{ladcharacter,jdbcType=VARCHAR},#{laddeliverynumber,jdbcType=NUMERIC},#{laddeliveryaddress,jdbcType=VARCHAR},#{laddrawer,jdbcType=VARCHAR},#{stockownername,jdbcType=VARCHAR},#{stocknumber,jdbcType=NUMERIC},#{stockno,jdbcType=VARCHAR},#{stocknature,jdbcType=VARCHAR},#{stockholdersname,jdbcType=VARCHAR},#{stocksecuritiesaccountno,jdbcType=VARCHAR},#{stockinstitutions,jdbcType=VARCHAR},#{stockregno,jdbcType=VARCHAR},#{stockinvestorsaccount,jdbcType=VARCHAR},#{stockcode,jdbcType=VARCHAR},#{stockpledgeref,jdbcType=VARCHAR},#{stockpledgenumber,jdbcType=VARCHAR},#{stockpledgeregdate,jdbcType=TIMESTAMP},#{intprotype,jdbcType=NUMERIC},#{intproownername,jdbcType=VARCHAR},#{intproproveorg,jdbcType=NUMERIC},#{intpropatentnumber,jdbcType=VARCHAR},#{intproregorg,jdbcType=VARCHAR},#{intproregdate,jdbcType=TIMESTAMP},#{intproteddate,jdbcType=TIMESTAMP},#{intproprovedate,jdbcType=TIMESTAMP},#{intpropledgeno,jdbcType=VARCHAR},#{intproorg,jdbcType=VARCHAR},#{chargeorg,jdbcType=VARCHAR},#{chargelicenceno,jdbcType=VARCHAR},#{chargelastdate,jdbcType=TIMESTAMP},#{chargeregorg,jdbcType=NUMERIC},#{accrecproveperson,jdbcType=VARCHAR},#{accrecowner,jdbcType=VARCHAR},#{accrecamountccy,jdbcType=CHAR},#{accrecamount,jdbcType=NUMERIC},#{carproveperson,jdbcType=VARCHAR},#{carproduction,jdbcType=VARCHAR},#{carno,jdbcType=VARCHAR},#{carbrand,jdbcType=VARCHAR},#{cartype,jdbcType=VARCHAR},#{carengineno,jdbcType=VARCHAR},#{carchassisno,jdbcType=VARCHAR},#{carprice,jdbcType=NUMERIC},#{accrec_remark,jdbcType=VARCHAR},#{bankaccount_remark,jdbcType=VARCHAR},#{car_remark,jdbcType=VARCHAR},#{cd_remark,jdbcType=VARCHAR},#{cer_remark,jdbcType=VARCHAR},#{charge_remark,jdbcType=VARCHAR},#{intpro_remark,jdbcType=VARCHAR},#{lad_remark,jdbcType=VARCHAR},#{notes_remark,jdbcType=VARCHAR},#{stock_remark,jdbcType=VARCHAR},#{wr_remark,jdbcType=VARCHAR},#{nd_remark,jdbcType=VARCHAR},#{other_remark,jdbcType=VARCHAR},#{chargeproject,jdbcType=VARCHAR},#{pledgecustcod,jdbcType=VARCHAR},#{nd_kind,jdbcType=NUMERIC},#{nd_id,jdbcType=VARCHAR},#{nd_rate,jdbcType=NUMERIC},#{nd_deadline,jdbcType=NUMERIC},#{nd_amount,jdbcType=NUMERIC},#{nd_bank,jdbcType=VARCHAR},#{nd_begindate,jdbcType=VARCHAR},#{nd_enddate,jdbcType=VARCHAR},#{nd_address,jdbcType=VARCHAR},#{other_name,jdbcType=VARCHAR},#{other_unit,jdbcType=VARCHAR},#{other_number,jdbcType=NUMERIC},#{other_address,jdbcType=VARCHAR},#{num,jdbcType=NUMERIC},#{stateid,jdbcType=NUMERIC},#{createtime,jdbcType=TIMESTAMP},#{refid,jdbcType=CHAR})"})
    void savePrendaInfo(Prenda_Entity prenda_entity);

    //update抵押信息
    @Update("update loanapplicantpledge set mode1=#{mode1,jdbcType=VARCHAR},amountccy=#{amountccy,jdbcType=CHAR},amount=#{amount,jdbcType=NUMERIC},valueccy=#{valueccy,jdbcType=CHAR},value=#{value,jdbcType=NUMERIC},contractnumber=#{contractnumber,jdbcType=VARCHAR},signeddate=#{signeddate,jdbcType=TIMESTAMP},bankaccountname=#{bankaccountname,jdbcType=VARCHAR},bankaccounttype=#{bankaccounttype,jdbcType=VARCHAR},bankname=#{bankname,jdbcType=VARCHAR},bankaccount=#{bankaccount,jdbcType=VARCHAR},bankaccountccy=#{bankaccountccy,jdbcType=CHAR},bankaccountbalance=#{bankaccountbalance,jdbcType=NUMERIC},bankaccountistrust=#{bankaccountistrust,jdbcType=NUMERIC},bankpledgename=#{bankpledgename,jdbcType=VARCHAR},bankpledgeownertype=#{bankpledgeownertype,jdbcType=NUMERIC},bankpledgeeffectivedate=#{bankpledgeeffectivedate,jdbcType=TIMESTAMP},bankpledgelastdate=#{bankpledgelastdate,jdbcType=TIMESTAMP},bankisfreezed=#{bankisfreezed,jdbcType=NUMERIC},ethnic=#{ethnic,jdbcType=VARCHAR},pidno=#{pidno,jdbcType=VARCHAR},mortgagorname=#{mortgagorname,jdbcType=VARCHAR},contactaddress=#{contactaddress,jdbcType=VARCHAR},contactphone=#{contactphone,jdbcType=VARCHAR},cerorg=#{cerorg,jdbcType=VARCHAR},cercustomer=#{cercustomer,jdbcType=VARCHAR},cerno=#{cerno,jdbcType=VARCHAR},ceraccount=#{ceraccount,jdbcType=VARCHAR},cereffectivedate=#{cereffectivedate,jdbcType=TIMESTAMP},cerlastdate=#{cerlastdate,jdbcType=TIMESTAMP},ceramount=#{ceramount,jdbcType=NUMERIC},cdname=#{cdname,jdbcType=VARCHAR},cdtype=#{cdtype,jdbcType=NUMERIC},cdno=#{cdno,jdbcType=VARCHAR},cdaccount=#{cdaccount,jdbcType=VARCHAR},cdbank=#{cdbank,jdbcType=VARCHAR},cdisselfbank=#{cdisselfbank,jdbcType=NUMERIC},cdamount=#{cdamount,jdbcType=NUMERIC},cdisstoppay=#{cdisstoppay,jdbcType=NUMERIC},notestype=#{notestype,jdbcType=NUMERIC},notesno=#{notesno,jdbcType=VARCHAR},notesamount=#{notesamount,jdbcType=NUMERIC},notesbank=#{notesbank,jdbcType=VARCHAR},notessigneddate=#{notessigneddate,jdbcType=TIMESTAMP},noteslastdate=#{noteslastdate,jdbcType=TIMESTAMP},notesaddress=#{notesaddress,jdbcType=VARCHAR},wrowner=#{wrowner,jdbcType=VARCHAR},wrorg=#{wrorg,jdbcType=VARCHAR},wrno=#{wrno,jdbcType=VARCHAR},wrname=#{wrname,jdbcType=VARCHAR},wrstandard=#{wrstandard,jdbcType=VARCHAR},wrunit=#{wrunit,jdbcType=VARCHAR},wrnumber=#{wrnumber,jdbcType=NUMERIC},wrdeliverystaff=#{wrdeliverystaff,jdbcType=VARCHAR},wracceptancestaff=#{wracceptancestaff,jdbcType=VARCHAR},wrgoodsvalue=#{wrgoodsvalue,jdbcType=NUMERIC},wrstoragecosts=#{wrstoragecosts,jdbcType=NUMERIC},wrhaveinsurance=#{wrhaveinsurance,jdbcType=NUMERIC},wrinsurancebegindate=#{wrinsurancebegindate,jdbcType=TIMESTAMP},wrinsuranceenddate=#{wrinsuranceenddate,jdbcType=TIMESTAMP},ladorg=#{ladorg,jdbcType=VARCHAR},ladno=#{ladno,jdbcType=VARCHAR},ladcontractno=#{ladcontractno,jdbcType=VARCHAR},ladcommoditycoding=#{ladcommoditycoding,jdbcType=VARCHAR},ladshippingunit=#{ladshippingunit,jdbcType=VARCHAR},ladgoods=#{ladgoods,jdbcType=VARCHAR},ladrule=#{ladrule,jdbcType=VARCHAR},ladcharacter=#{ladcharacter,jdbcType=VARCHAR},laddeliverynumber=#{laddeliverynumber,jdbcType=NUMERIC},laddeliveryaddress=#{laddeliveryaddress,jdbcType=VARCHAR},laddrawer=#{laddrawer,jdbcType=VARCHAR},stockownername=#{stockownername,jdbcType=VARCHAR},stocknumber=#{stocknumber,jdbcType=NUMERIC},stockno=#{stockno,jdbcType=VARCHAR},stocknature=#{stocknature,jdbcType=VARCHAR},stockholdersname=#{stockholdersname,jdbcType=VARCHAR},stocksecuritiesaccountno=#{stocksecuritiesaccountno,jdbcType=VARCHAR},stockinstitutions=#{stockinstitutions,jdbcType=VARCHAR},stockregno=#{stockregno,jdbcType=VARCHAR},stockinvestorsaccount=#{stockinvestorsaccount,jdbcType=VARCHAR},stockcode=#{stockcode,jdbcType=VARCHAR},stockpledgeref=#{stockpledgeref,jdbcType=VARCHAR},stockpledgenumber=#{stockpledgenumber,jdbcType=VARCHAR},stockpledgeregdate=#{stockpledgeregdate,jdbcType=TIMESTAMP},intprotype=#{intprotype,jdbcType=NUMERIC},intproownername=#{intproownername,jdbcType=VARCHAR},intproproveorg=#{intproproveorg,jdbcType=NUMERIC},intpropatentnumber=#{intpropatentnumber,jdbcType=VARCHAR},intproregorg=#{intproregorg,jdbcType=VARCHAR},intproregdate=#{intproregdate,jdbcType=TIMESTAMP},intproteddate=#{intproteddate,jdbcType=TIMESTAMP},intproprovedate=#{intproprovedate,jdbcType=TIMESTAMP},intpropledgeno=#{intpropledgeno,jdbcType=VARCHAR},intproorg=#{intproorg,jdbcType=VARCHAR},chargeorg=#{chargeorg,jdbcType=VARCHAR},chargelicenceno=#{chargelicenceno,jdbcType=VARCHAR},chargelastdate=#{chargelastdate,jdbcType=TIMESTAMP},chargeregorg=#{chargeregorg,jdbcType=NUMERIC},accrecproveperson=#{accrecproveperson,jdbcType=VARCHAR},accrecowner=#{accrecowner,jdbcType=VARCHAR},accrecamountccy=#{accrecamountccy,jdbcType=CHAR},accrecamount=#{accrecamount,jdbcType=NUMERIC},carproveperson=#{carproveperson,jdbcType=VARCHAR},carproduction=#{carproduction,jdbcType=VARCHAR},carno=#{carno,jdbcType=VARCHAR},carbrand=#{carbrand,jdbcType=VARCHAR},cartype=#{cartype,jdbcType=VARCHAR},carengineno=#{carengineno,jdbcType=VARCHAR},carchassisno=#{carchassisno,jdbcType=VARCHAR},carprice=#{carprice,jdbcType=NUMERIC},accrec_remark=#{accrec_remark,jdbcType=VARCHAR},car_remark=#{car_remark,jdbcType=VARCHAR},bankaccount_remark=#{bankaccount_remark,jdbcType=VARCHAR},cd_remark=#{cd_remark,jdbcType=VARCHAR},cer_remark=#{cer_remark,jdbcType=VARCHAR},charge_remark=#{charge_remark,jdbcType=VARCHAR},intpro_remark=#{intpro_remark,jdbcType=VARCHAR},lad_remark=#{lad_remark,jdbcType=VARCHAR},notes_remark=#{notes_remark,jdbcType=VARCHAR},stock_remark=#{stock_remark,jdbcType=VARCHAR},wr_remark=#{wr_remark,jdbcType=VARCHAR},nd_remark=#{nd_remark,jdbcType=VARCHAR},other_remark=#{other_remark,jdbcType=VARCHAR},chargeproject=#{chargeproject,jdbcType=VARCHAR},pledgecustcod=#{pledgecustcod,jdbcType=VARCHAR},nd_kind=#{nd_kind,jdbcType=NUMERIC},nd_id=#{nd_id,jdbcType=VARCHAR},nd_rate=#{nd_rate,jdbcType=NUMERIC},nd_deadline=#{nd_deadline,jdbcType=NUMERIC},nd_amount=#{nd_amount,jdbcType=NUMERIC},nd_bank=#{nd_bank,jdbcType=VARCHAR},nd_begindate=#{nd_begindate,jdbcType=VARCHAR},nd_enddate=#{nd_enddate,jdbcType=VARCHAR},nd_address=#{nd_address,jdbcType=VARCHAR},other_name=#{other_name,jdbcType=VARCHAR},other_unit=#{other_unit,jdbcType=VARCHAR},other_number=#{other_number,jdbcType=NUMERIC},other_address=#{other_address,jdbcType=VARCHAR},num=#{num,jdbcType=NUMERIC},stateid=#{stateid,jdbcType=NUMERIC},createtime=#{createtime,jdbcType=TIMESTAMP},refid=#{refid,jdbcType=CHAR} where id=#{id}")
    void updatePrendaInfo(Prenda_Entity prenda_entity);

    //删除抵押信息
    @Delete("delete from loanapplicantpledge where id=#{id}")
    void deletePrendaInfo(String id);
}
